﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace Abp.Utils
{
    /// <summary>
    /// 
    /// </summary>
    public static class NPOIUtils
    {

        public const string ExcelContentType = "application/vnd.ms-excel|application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        public static IWorkbook GetWorkbookFromStream(HttpPostedFileBase uploadFile)
        {
            IWorkbook workbook = null;
            if (ExcelContentType.IndexOf(uploadFile.ContentType) == 0)
                workbook = new HSSFWorkbook(uploadFile.InputStream);
            else
                workbook = new XSSFWorkbook(uploadFile.InputStream);
            return workbook;
        }
        public static string GetString(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            try
            {
                switch (cell.CellType)
                {
                    case CellType.Blank:
                        return string.Empty;
                    case CellType.Boolean:
                        return cell.BooleanCellValue.ToString();
                    case CellType.Formula:
                        return cell.CellFormula;
                    case CellType.Numeric:
                        return cell.NumericCellValue.ToString();
                    default:
                        return cell.StringCellValue.Trim().Replace("\r", "").Replace("\n", "");

                }

            }
            catch
            {
                return Conv.ToString(cell).Trim().Replace("\r", "").Replace("\n", "");
            }
        }
        public static DateTime GetDateTime(ICell cell)
        {
            if (cell == null)
                return DateTime.MinValue;
            try
            {
                return cell.DateCellValue;
            }
            catch
            {
                return Conv.ToDateTime(GetString(cell));
            }
        }
        public static string GetSimpleString(ICell cell)
        {
            return GetString(cell).Replace(" ", "");
        }
        public static int GetInt(ICell cell)
        {
            if (cell == null)
                return 0;
            try
            {
                return Conv.ToInt32(cell.NumericCellValue);
            }
            catch
            {
                return Conv.ToInt32(GetString(cell));
            }

        }

        public static double GetDouble(ICell cell)
        {
            if (cell == null)
                return 0f;
            try
            {
                return cell.NumericCellValue;
            }
            catch
            {
                return Conv.ToDouble(GetString(cell));
            }
        }

        public static decimal GetDecimal(ICell cell)
        {
            if (cell == null)
                return 0;
            try
            {
                return Conv.ToDecimal(cell.NumericCellValue);
            }
            catch
            {
                return Conv.ToDecimal(GetString(cell));
            }
        }

        public static bool GetBoolean(ICell cell)
        {
            if (cell == null)
                return false;
            try
            {
                return cell.BooleanCellValue;
            }
            catch
            {
                string value = GetSimpleString(cell);
                if (value.ToLower() == "y" || value.ToLower() == "yes" || value == "是")
                    return true;
                if (value.ToLower() == "n" || value.ToLower() == "no" || value == "否")
                    return false;
                return Conv.ToBoolean(value);
            }
        }

        public static decimal ToDecimal(ICell cell)
        {
            if (cell == null)
                return 0M;
            try
            {
                return Convert.ToDecimal(cell.NumericCellValue);
            }
            catch
            {
                return Conv.ToDecimal(GetString(cell));
            }
        }

        public static Guid? GetGuidOrNull(ICell cell)
        {
            string value = GetString(cell);
            if (string.IsNullOrWhiteSpace(value))
                return null;
            return Conv.ToGuidOrNull(value);

        }
        public static Guid GetGuid(ICell cell)
        {
            string value = GetString(cell);
            if (string.IsNullOrWhiteSpace(value))
                return Guid.Empty;
            return Conv.ToGuid(value);

        }
    }




}
